package xyh.ContributionSystem.Projects;

import xyh.ContributionSystem.ContributionSearch.SearchProject;
//import xyh.DAO.DAOBase;
import Connection.DAOBase;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProjectDAOImpl extends DAOBase implements ProjectDAO {
    private static final String PROJECT_INSERT_SQL = "INSERT INTO pro (p_id, p_name, teacher_id, p_type,p_time,p_task,p_outlay) VALUES(?,?,?,?,?,?,?)";
    private static final String PROJECT_UPDATE_SQL = "UPDATE pro SET p_name = ?, teacher_id = ?, p_type = ?,p_time = ?,p_task = ?,p_outlay = ? WHERE p_id = ?";
    private static final String PROJECT_SELECT_SQL = "SELECT * FROM pro WHERE p_id = ?";
    private static final String PROJECT_SELECT_ALL_SQL = "SELECT * FROM pro";

    @Override
    public boolean addProject(Project project) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(PROJECT_INSERT_SQL);
            psmt.setInt(1, project.getP_id());
            psmt.setString(2, project.getP_name());
            psmt.setInt(3, project.getTeacher_id());
            psmt.setString(4, project.getP_type());
            psmt.setString(5, project.getP_time());
            psmt.setString(6, project.getP_task());
            psmt.setDouble(7, project.getP_outlay());
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public boolean updateProject(Project project) {
        Connection con = null;
        try {
            con = getConnection();
            // 创建预编译语句对象
            PreparedStatement psmt = con.prepareStatement(PROJECT_UPDATE_SQL);
            // 设置参数值
            psmt.setString(1, project.getP_name());
            psmt.setInt(2, project.getTeacher_id());
            psmt.setString(3, project.getP_type());
            psmt.setString(4, project.getP_time());
            psmt.setString(5, project.getP_task());
            psmt.setDouble(6, project.getP_outlay());
            psmt.setInt(7, project.getP_id());
            // 执行更新操作
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public Project getProject(int project_id) {
        Project project = null;
        Connection con = null;
        try {
            con = getConnection();
            // 创建预编译语句对象
            PreparedStatement psmt = con.prepareStatement(PROJECT_SELECT_SQL);
            // 设置参数值
            psmt.setString(1, String.valueOf(project_id));
            // 执行查询操作
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                // 从结果集中读取数据，并封装成 Project 对象
                project = new Project();
                project.setP_id(rs.getInt("p_id"));
                project.setP_name(rs.getString("p_name"));
                project.setTeacher_id(rs.getInt("teacher_id"));
                project.setP_type(rs.getString("p_type"));
                project.setP_time(rs.getString("p_time"));
                project.setP_task(rs.getString("p_task"));
                project.setP_outlay(rs.getDouble("p_outlay"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {e.printStackTrace();}
        }
        return project;
    }

    @Override
    public List<Project> getAllProjects() {
        List<Project> projects = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement statement = con.createStatement();
            ResultSet rs = statement.executeQuery(PROJECT_SELECT_ALL_SQL);
            while (rs.next()) {
                Project project = new Project();
                project.setP_id(rs.getInt("p_id"));
                project.setP_name(rs.getString("p_name"));
                project.setTeacher_id(rs.getInt("teacher_id"));
                project.setP_type(rs.getString("p_type"));
                project.setP_time(rs.getString("p_time"));
                project.setP_task(rs.getString("p_task"));
                project.setP_outlay(rs.getDouble("p_outlay"));
                projects.add(project);
            }
            rs.close();
            statement.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return projects;
    }

    @Override
    public List<Project> findProject(SearchProject searchProject) {
        List<Project> projects = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM pro WHERE 1=1");
            if (searchProject.getP_id() != 0) {
                sqlBuilder.append(" AND p_id = ?");
            }
            if (searchProject.getP_name() != null) {
                sqlBuilder.append(" AND p_name LIKE ?");
            }
            if (searchProject.getTeacher_id() != 0) {
                sqlBuilder.append(" AND teacher_id = ?");
            }
            if (searchProject.getP_type() != null) {
                sqlBuilder.append(" AND p_type LIKE ?");
            }
            if (searchProject.getP_time() != null) {
                sqlBuilder.append(" AND p_time = ?");
            }
            if (searchProject.getP_task() != null) {
                sqlBuilder.append(" AND p_task LIKE ?");
            }
            if (searchProject.getP_outlay() > 0) {
                sqlBuilder.append(" AND p_outlay = ?");
            }
            String sql = sqlBuilder.toString();

            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchProject.getP_id() != 0) {
                psmt.setInt(index++, searchProject.getP_id());
            }
            if (searchProject.getP_name() != null) {
                psmt.setString(index++, "%" + searchProject.getP_name() + "%");
            }
            if (searchProject.getTeacher_id() != 0) {
                psmt.setInt(index++, searchProject.getTeacher_id());
            }
            if (searchProject.getP_type() != null) {
                psmt.setString(index++, searchProject.getP_type());
            }
            if (searchProject.getP_time() != null) {
                psmt.setString(index++, searchProject.getP_time());
            }
            if (searchProject.getP_task() != null) {
                psmt.setString(index++, "%" + searchProject.getP_task() + "%");
            }
            if (searchProject.getP_outlay() > 0) {
                psmt.setDouble(index++, searchProject.getP_outlay());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                int p_id = rs.getInt("p_id");
                String p_name = rs.getString("p_name");
                int teacher_id = rs.getInt("teacher_id");
                String p_type = rs.getString("p_type");
                String p_time = rs.getString("p_time");
                String p_task = rs.getString("p_task");
                double p_outlay = rs.getDouble("p_outlay");
                Project project = new Project(p_id, p_name, teacher_id, p_type, p_time, p_task, p_outlay);
                projects.add(project);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return projects;
    }


}
